﻿using apitest.Application.Entities.Pms;
using MiniExcelLibs;

namespace apitest.Application.Test.Services
{
    [DynamicApiController]
    public class Test4Service
    {
        ISqlSugarClient _db;
        ISqlSugarClient _testDb;

        public Test4Service(ISqlSugarClient db)
        {
            _db = db;
            _testDb = db.AsTenant().GetConnection("test");
        }

        [HttpGet]
        public async Task<dynamic> T1()
        {
            var list = await _db.Queryable<Production_Device>().ToListAsync();
            var list1 = MiniExcel.Query<Device>("555.xlsx", "Sheet1");
            var res = new List<dynamic>();
            var codeList = list1.Select(o => o.code).ToList();
            var a = codeList.Contains("P-R1007");
            foreach (var item in list)
            {
                var device = list1.FirstOrDefault(o => o.code == item.Code);
                if (device == null)
                {

                    res.Add(new
                    {
                        item.Code,
                        item.Site
                    });
                }
                else
                {
                    await _db.Updateable<Production_Device>()
                        .SetColumns(o => new Production_Device()
                        {
                            Price = Math.Round(device.price / 24 / 60, 2)
                        })
                        .Where(o => o.Code == device.code)
                        .ExecuteCommandAsync();
                }
            }
            return res;
        }

        public async Task T2()
        {
            var list = await _db.Queryable<Production_Record_Device>()
                .Where(o => o.Duration == null)
                .ToListAsync();
            foreach (var item in list)
            {
                await _db.Updateable<Production_Record_Device>()
                    .SetColumns(o => new Production_Record_Device()
                    {
                        Duration = DateHelper.GetDateDiff(item.StartTime.Value, item.EndTime.Value, 2)
                    }).Where(o => o.Id == item.Id)
                    .ExecuteCommandAsync();
            }
        }

        [HttpGet, QueryParameters]
        public async Task<dynamic> T3(string batch)
        {
            var b = await _db.Queryable<Production_Batch>().FirstAsync(o => o.Batch == batch);
            var projectCode = b.ProjectCode;
            var sql1 = $@"WITH RankedMaterials AS (
                                select 
                                t.FDate, -- 采购日期
                                m1.FNUMBER AS MaterialCode, -- 物料编码
                                m2.fname AS MaterialName, -- 物料名称
                                m1.F_SJVC_CAS AS cas,
                                m1.F_SJVC_CATLOG AS cat,
                                D.FQty AS amount, -- 采购数量
                                u.FNAME AS Unit, -- 采购单位
                                dF.FTAXAMOUNT, -- 税额
                                df.FAMOUNT, -- 金额
                                df.FALLAMOUNT, -- 价税合计
                                df.FTAXPRICE AS UnitPrice, -- 含税单价
                                p.FDATAVALUE  as ProjectCode,--项目号
                                 ROW_NUMBER() OVER (PARTITION BY m1.FNUMBER ORDER BY t.FDate DESC, d.fid DESC) AS RowNum -- 按采购日期和FID排序
                                from T_PUR_POORDERENTRY d
                                LEFT JOIN T_PUR_POORDER t ON t.fid = d.fid
                                LEFT JOIN T_BD_MATERIAL m1 ON m1.FMATERIALID = D.FMATERIALID
                                LEFT JOIN T_BD_MATERIAL_L m2 ON m2.FMATERIALID = D.FMATERIALID AND m2.FLOCALEID = 2052
                                LEFT JOIN T_BD_UNIT_L u ON u.FUNITID = D.FUnitId AND u.FLOCALEID = 2052
                                LEFT JOIN T_PUR_POORDERENTRY_F df ON df.fentryid = d.fentryid
                                left join T_BAS_ASSISTANTDATAENTRY_L p on d.F_SJVC_YQXMH=p.fentryid
                                where  p.fdatavalue= '{projectCode}' 
                                )
                                  SELECT
                                    FDate,
                                    MaterialCode,
                                    MaterialName,
                                --     cas,
                                --     cat,
                                --     amount,
                                    Unit,
                                -- 		FTAXAMOUNT, -- 税额
                                -- 		FAMOUNT, -- 金额
                                -- 		FALLAMOUNT, -- 价税合计
                                    UnitPrice,
								 	ProjectCode
                                FROM
                                    RankedMaterials
																	
                                WHERE
                                    RowNum = 1;";
            var _erpDb = _db.AsTenant().GetConnection("erp");

            var priceList = await _erpDb.SqlQueryable<MaterialPrice>(sql1).ToListAsync();
            var progressIds = await _db.Queryable<Production_Record_Progress>().Where(o => o.Batch == batch)
                .Select(o => o.Id).ToListAsync();
            var materialList = await _db.Queryable<Production_Record_Material>()
                .Where(o => progressIds.Contains(o.ProgressId))
                .ToListAsync();
            foreach (var item in materialList)
            {

                var ma = priceList.FirstOrDefault(o => o.MaterialName == item.MaterialName);
                if (ma != null)
                {
                    await _db.Updateable<Production_Record_Material>()
                        .SetColumns(o => new Production_Record_Material()
                        {
                            MaterialCode = ma.MaterialCode
                        }).Where(o => o.Id == item.Id)
                        .ExecuteCommandAsync();
                }
            }
            return "";
            return await _db.Queryable<Production_Record_Material>()
                .ToListAsync();
        }

        public async Task<dynamic> T4()
        {
            var list = await _db.Queryable<Production_Batch>()
                .ToListAsync();
            foreach (var item in list)
            {
                await T3(item.Batch);
            }
            return "";
        }

        public class Device
        {
            public string code { get; set; }
            public double day { get; set; }
            public double price { get; set; }
            public string company { get; set; }
        }

        public class MaterialPrice
        {
            public DateTime? FDate { get; set; }
            public string MaterialCode { get; set; }
            public string MaterialName { get; set; }
            public string Unit { get; set; }
            public double UnitPrice { get; set; }
        }
    }
}
